from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from commons.entity.po.approval import Approval
from commons.entity.po.reimbursement import Reimbursement
from commons.entity.po.schedule import Schedule
from commons.entity.po.travel import Travel
from commons.mappers.base_mapper import BaseMapper

# 创建数据库引擎
db_url = "mysql+pymysql://root:123456@localhost/ctdb"
engine = create_engine(db_url, echo=True)

# 创建会话类
Session = sessionmaker(bind=engine)
session = Session()


class ReportMapper(BaseMapper):
    def __init__(self):
        super().__init__(Session)

    def select_sum_count_by_date(self, start_date, end_date):
        return session.query(Reimbursement).filter(Reimbursement.apply_date.between(start_date, end_date)).all()

    def select_approval_by_date(self, start_date, end_date):
        return session.query(Approval).join(Travel).filter(Travel.apply_date.between(start_date, end_date)).all()

    def select_schedule_by_date(self, start_date, end_date):
        return session.query(Schedule).filter(Schedule.start_date.between(start_date, end_date)).all()
